using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for DataAccessClass
/// </summary>
public class DataAccessClass
{
    public DataAccessClass()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public static SqlConnection CreateConnection()
    {
        string constr = ConfigurationManager.ConnectionStrings["TMSCon"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);
        return con;
    }

    internal static DataTable GetUserDetails(string UserName, string Password, int RoleId)
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@UserName", UserName);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@Password", Password);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@RoleId", RoleId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            dt = ExecuteDataTable(lstparam, "P_ValidateUser");
            return dt;

        }
        catch (Exception e)
        {
            throw e;
        }
    }

    private static DataTable ExecuteDataTable(List<SqlParameter> param, string procuder)
    {
        SqlConnection con;
        SqlDataReader dr;
        SqlCommand cmd;
        DataTable dt;
        con = CreateConnection();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        dt = new DataTable();
        if (param != null && param.Count > 0)
        {
            foreach (SqlParameter p in param)
            {
                cmd.Parameters.Add(p);
            }
        }
        cmd.CommandText = procuder;
        try
        {

            con.Open();
            dr = cmd.ExecuteReader(CommandBehavior.Default);
            dt.Load(dr, LoadOption.OverwriteChanges);

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            con.Close();
        }
        return dt;

    }

    private static DataSet ExecuteDataSet(List<SqlParameter> param, string procedure)
    {
        SqlConnection con;
        SqlDataAdapter sqlDA;
        SqlCommand cmd;
        DataSet ds;
        con = CreateConnection();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        ds = new DataSet();
        if (param != null && param.Count > 0)
        {
            foreach (SqlParameter p in param)
            {
                cmd.Parameters.Add(p);
            }
        }
        cmd.CommandText = procedure;
        try
        {
            con.Open();
            sqlDA = new SqlDataAdapter(cmd);
            sqlDA.Fill(ds);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            con.Close();
        }
        return ds;

    }

    private static int ExecuteNonQuery(List<SqlParameter> param, string procuder)
    {
        SqlConnection con;
        int result = 0;
        SqlCommand cmd;
        con = CreateConnection();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;

        if (param.Count > 0)
        {
            foreach (SqlParameter p in param)
            {
                cmd.Parameters.Add(p);
            }
        }
        cmd.CommandText = procuder;
        try
        {

            con.Open();
            result = cmd.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            con.Close();
            //  dr.Close();
        }
        return result;
    }

    internal static DataTable GetLookupData(string tableName)
    {
        DataTable dt;
        List<SqlParameter> lstparam = new List<SqlParameter>();
        SqlParameter param;
        param = new SqlParameter("@TableName", tableName);
        param.DbType = DbType.String;
        lstparam.Add(param);
        dt = ExecuteDataTable(lstparam, "P_GetLookupValues");
        return dt;
    }

    public static int CreateCompany(string compId, string type, string compName, string address, string contactNo, string email, string vat, byte[] logo)
    {
        try
        {
            int res = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@CompanyId", Convert.ToInt32(compId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyType", Convert.ToInt32(type));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyName", compName);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyAddress", address);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyContactNo", contactNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyEmail", email);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CompanyVatNo", vat);
            param.DbType = DbType.String;
            lstparam.Add(param);
            if (logo.Length > 0)
            {
                param = new SqlParameter("@CompanyLogo", logo);
                param.DbType = DbType.Binary;
                lstparam.Add(param);
            }
            param = new SqlParameter("@UserId", 1);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            res = ExecuteNonQuery(lstparam, "P_CreateCompany");
            return res;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetCompanyDetails(int roleId, int compId)
    {
        DataTable dt;
        List<SqlParameter> lstparam = new List<SqlParameter>();
        SqlParameter p;
        p = new SqlParameter("@RoleId", roleId);
        p.DbType = DbType.Int32;
        lstparam.Add(p);
        p = new SqlParameter("@CompId", compId);
        p.DbType = DbType.Int32;
        lstparam.Add(p);
        dt = ExecuteDataTable(lstparam, "P_GetCompanyDetails");
        return dt;
    }

    public static DataTable GetLogo(int compId, string type, int imgNo)
    {
        DataTable dt;
        List<SqlParameter> lstparam = new List<SqlParameter>();
        SqlParameter p;
        p = new SqlParameter("@PrimaryId", compId);
        p.DbType = DbType.Int32;
        lstparam.Add(p);
        p = new SqlParameter("@Type", type);
        p.DbType = DbType.String;
        lstparam.Add(p);
        p = new SqlParameter("@ImageNo", imgNo);
        p.DbType = DbType.Int32;
        lstparam.Add(p);
        dt = ExecuteDataTable(lstparam, "P_GetImage");
        return dt;
    }

    public static DataTable ListUser(int roleId, int userId)
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p = new SqlParameter("@UserId", userId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            dt = ExecuteDataTable(lstparam, "P_ListUser");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int UpdateUserProfile(int userid, string name, string address)
    {
        try
        {

            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@UserId", userid);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@Name", name);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@Address", address);
            param.DbType = DbType.String;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_CreateUser");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int UpdatePassword(int userId, string txtNewPwd)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@UserId", userId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@Password", txtNewPwd);
            param.DbType = DbType.String;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_UpdatePassword");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetCompany(BusinessClass.CompanyType companyType)
    {
        try
        {
            DataTable dt;
            int compType = 0;
            compType = (int)companyType;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p = new SqlParameter("@CompanyType", compType);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            dt = ExecuteDataTable(lstparam, "P_GetCompany");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int CreateVehicle(string date, string modelNo, string truckNo, int userId, int vehicleId)
    {
        try
        {
            //        @UserId INT,
            // DATETIME,
            //@ VARCHAR(50),
            //@ VARCHAR(50),
            //@ INT =0
            int result = 0;
            DateTime dt;
            dt = Convert.ToDateTime(date);
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@UserId", userId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@PurDate", dt);
            param.DbType = DbType.DateTime;
            lstparam.Add(param);
            param = new SqlParameter("@VehicleNo", truckNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@VehicleModelNo", modelNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@VehicleId", vehicleId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_CreateVehicle");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetTruck()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            dt = ExecuteDataTable(lstparam, "P_GetVehicle");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int CreateUser(string userName, string password, string name, string address, string role, string createdby, string userId)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@UserId", 0);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@UserName", userName);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@Password", password);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@Role", Convert.ToInt32(role));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@Name", name);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@Address", address);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CreatedBy", Convert.ToInt32(createdby));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_CreateUser");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetVehicleDetails(int roleId, int vehicleId)
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p = new SqlParameter("@VehicleId", vehicleId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            dt = ExecuteDataTable(lstparam, "P_GetVehicle");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int CreateEmployee(string empId, string empName, string empNo, string empAdd, string contactNo, byte[] imageIdProof, byte[] imageLicense, int createdBy)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@EmpId", Convert.ToInt32(empId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@EmpNo", empNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@EmpName", empName);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@EmpAddress", empAdd);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@EmpContactNo", Convert.ToInt64(contactNo));
            param.DbType = DbType.Int64;
            lstparam.Add(param);
            param = new SqlParameter("@UserId", Convert.ToInt32(createdBy));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            if (imageIdProof.Length > 0)
            {
                param = new SqlParameter("@IdProof", imageIdProof);
                param.DbType = DbType.Binary;
                lstparam.Add(param);
            }
            if (imageLicense.Length > 0)
            {
                param = new SqlParameter("@Licence", imageLicense);
                param.DbType = DbType.Binary;
                lstparam.Add(param);
            }
            result = ExecuteNonQuery(lstparam, "P_CreateEmployee");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetEmployeeDetails(int roleId, int empId)
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p;
            p = new SqlParameter("@RoleId", roleId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@EmpId", empId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            dt = ExecuteDataTable(lstparam, "P_GetEmployeeDetails");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetPendingCompany()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();

            dt = ExecuteDataTable(lstparam, "P_GetPendingCompanies");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int ApproveDetails(string itemType, int itemId, int userId, int status, string comment)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p;
            p = new SqlParameter("@ItemId", itemId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@Status", status);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@UserId", userId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@ItemType", itemType);
            p.DbType = DbType.String;
            lstparam.Add(p);
            p = new SqlParameter("@Comment", comment);
            p.DbType = DbType.String;
            lstparam.Add(p);
            result = ExecuteNonQuery(lstparam, "P_ApproveDetails");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int DeleteDetails(string itemType, int itemId, int userId)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p;
            p = new SqlParameter("@ItemId", itemId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@UserId", userId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            p = new SqlParameter("@ItemType", itemType);
            p.DbType = DbType.String;
            lstparam.Add(p);
            result = ExecuteNonQuery(lstparam, "P_DeleteMaster");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    public static DataTable GetPendingVehicle()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();

            dt = ExecuteDataTable(lstparam, "P_GetPendingVehicle");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetPendingEmployees()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();

            dt = ExecuteDataTable(lstparam, "P_GetPendingEmployees");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetPendingChallan()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();

            dt = ExecuteDataTable(lstparam, "P_GetPendingCChallans");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetEmployee()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            dt = ExecuteDataTable(lstparam, "P_GetEmployee");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }



    internal static int CreateChallan(int ChallanId, int ChallanNo, DateTime ChallanDate, int SuppCompId, int ContractorCompId, int TransportCompId, decimal CFT, int DriverId, int CleanerId, int TruckId, int MaterialId, byte[] ChallanCopy, int UserId,string ticketNo)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@ChallanId", Convert.ToInt32(ChallanId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@ChallanNo", ChallanNo);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@ChallanDate", ChallanDate);
            param.DbType = DbType.DateTime;
            lstparam.Add(param);
            param = new SqlParameter("@SuppCompId", SuppCompId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@ContractorCompId", ContractorCompId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@TransportCompId", TransportCompId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@UserId", Convert.ToInt32(UserId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@TruckId", Convert.ToInt32(TruckId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            if (ChallanCopy.Length > 0)
            {
                param = new SqlParameter("@ChallanCopy", ChallanCopy);
                param.DbType = DbType.Binary;
                lstparam.Add(param);
            }
            param = new SqlParameter("@DriverId", DriverId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@CleanerId", CleanerId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@MaterialId", MaterialId);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@CFT", CFT);
            param.DbType = DbType.Decimal;
            lstparam.Add(param);
            param = new SqlParameter("@TicketNo", ticketNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_CreateChallan");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }



    internal static DataTable GetChallanDetails(int challanId)
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p;
            p = new SqlParameter("@ChallanId", challanId);
            p.DbType = DbType.Int32;
            lstparam.Add(p);
            dt = ExecuteDataTable(lstparam, "P_GetChallanDetails");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetChallanDetails(string challandate, string transportcomp, string challanno, int challanId, int status)
    {

        try
        {
            DataTable dt;
            DateTime dat;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter p;
            if (DateTime.TryParse(challandate, out dat))
            {
                p = new SqlParameter("@ChallanDate", dat);
                p.DbType = DbType.DateTime;
                lstparam.Add(p);
            }
            int chNo = 0;
            if (Int32.TryParse(challanno, out chNo))
            {
                p = new SqlParameter("@ChallanNo", chNo);
                p.DbType = DbType.Int32;
                lstparam.Add(p);
            }
            if (!string.IsNullOrEmpty(transportcomp))
            {
                p = new SqlParameter("@TransPortComp", transportcomp);
                p.DbType = DbType.String;
                lstparam.Add(p);
            }
            if (challanId > 0)
            {
                p = new SqlParameter("@ChallanId", challanId);
                p.DbType = DbType.Int32;
                lstparam.Add(p);
            }
            if (status >= 0)
            {
                p = new SqlParameter("@Status", status);
                p.DbType = DbType.Int32;
                lstparam.Add(p);
            }

            dt = ExecuteDataTable(lstparam, "P_ListChallanDetails");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetMaterialDetails(int roleId, int mId)
    {
        try
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();
            SqlParameter p;
            if (mId > 0)
            {
                p = new SqlParameter("@MaterialId", mId);
                p.DbType = DbType.Int32;
                lstParam.Add(p);
            }
            DataTable dt = new DataTable();
            dt = ExecuteDataTable(lstParam, "P_GetMaterial");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetMaterial()
    {
        try
        {
            List<SqlParameter> sp = new List<SqlParameter>();
            SqlParameter p = new SqlParameter("@IsDDlFill", 1);
            p.DbType = DbType.Int16;
            sp.Add(p);
            DataTable dt = new DataTable();
            dt = ExecuteDataTable(sp, "P_GetMaterial");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }



    public static int CreateMaterial(int mId, string materialName, string description, int userId)
    {
        try
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();
            SqlParameter p;

            p = new SqlParameter("@MaterialId", mId);
            p.DbType = DbType.Int32;
            lstParam.Add(p);
            p = new SqlParameter("@MaterialName", materialName);
            p.DbType = DbType.String;
            lstParam.Add(p);
            p = new SqlParameter("@Description", description);
            p.DbType = DbType.String;
            lstParam.Add(p);
            p = new SqlParameter("@UserId", userId);
            p.DbType = DbType.Int32;
            lstParam.Add(p);
            int res = 0;
            res = ExecuteNonQuery(lstParam, "P_CreateMaterial");
            return res;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetPendingMaterial()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();

            dt = ExecuteDataTable(lstparam, "P_GetPendingMaterial");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    internal static DataTable GetBillDetails(string FromDate, string ToDate, string TransportCompId, string ContractorCompId, string Material)
    {
        try
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();
            SqlParameter p;
            DateTime frmDate, tDate;

            DataTable dt = new DataTable();
            p = new SqlParameter("@Material", Material);
            p.DbType = DbType.String;
            lstParam.Add(p);
            if (DateTime.TryParse(FromDate, out frmDate))
            {
                p = new SqlParameter("@FromDate", frmDate);
                p.DbType = DbType.DateTime;
                lstParam.Add(p);
            }
            if (DateTime.TryParse(ToDate, out tDate))
            {
                p = new SqlParameter("@ToDate", tDate);
                p.DbType = DbType.DateTime;
                lstParam.Add(p);
            }

            p = new SqlParameter("@TransportComp", TransportCompId);
            p.DbType = DbType.String;
            lstParam.Add(p);
            p = new SqlParameter("@ContractorComp", ContractorCompId);
            p.DbType = DbType.String;
            lstParam.Add(p);
            dt = ExecuteDataTable(lstParam, "P_ListBillDetails");
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataSet GenerateBill(string xmlString = null, int billNo = 0, decimal rate = 0, string blDate="")
    {
        try
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();
            SqlParameter p;
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            p = new SqlParameter("@XMLChallanNos", xmlString);
            p.DbType = DbType.Xml;
            lstParam.Add(p);
            if (billNo > 0)
            {
                p = new SqlParameter("@BillNo", billNo);
                p.DbType = DbType.Int32;
                lstParam.Add(p);
            }
            p = new SqlParameter("@Rate", rate);
            p.DbType = DbType.Decimal;
            lstParam.Add(p);
           DateTime  billDate;
            if(DateTime.TryParse(blDate,out billDate))
            {
            p = new SqlParameter("@BillDate", billDate);
            p.DbType = DbType.DateTime;
            lstParam.Add(p);
            }
            ds = ExecuteDataSet(lstParam, "P_GenerateBill");
            //  ds.Tables.Add(dt);
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetBillNos(bool isAdmin, int transportComp)
    {
        try
        {
            List<SqlParameter> lstParam = new List<SqlParameter>();
            SqlParameter p;
            DataTable dt = new DataTable();
            p = new SqlParameter("@IsAdminView", isAdmin);
            p.DbType = DbType.Byte;
            lstParam.Add(p);
            p = new SqlParameter("@TransComp", transportComp);
            p.DbType = DbType.Int32;
            lstParam.Add(p);
            dt = ExecuteDataTable(lstParam, "P_ListBillNos");
            //  ds.Tables.Add(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }


    public static String translateWholeNumber(String number)
    {
        string word = "";
        try
        {
            bool beginsZero = false;//tests for 0XX 
            bool isDone = false;//test if already translated 
            double dblAmt = (Convert.ToDouble(number));
            //if ((dblAmt > 0) && number.StartsWith("0")) 
            if (dblAmt > 0)
            {//test for zero or digit zero in a nuemric 
                beginsZero = number.StartsWith("0");
                int numDigits = number.Length;
                int pos = 0;//store digit grouping 
                String place = "";//digit grouping name:hundres,thousand,etc... 
                switch (numDigits)
                {
                    case 1://ones' range 
                        word = ones(number);
                        isDone = true;
                        break;
                    case 2://tens' range 
                        word = tens(number);
                        isDone = true;
                        break;
                    case 3://hundreds' range 
                        pos = (numDigits % 3) + 1;
                        place = " Hundred ";
                        break;
                    case 4://thousands' range 
                    case 5:
                    case 6:
                        pos = (numDigits % 4) + 1;
                        place = " Thousand ";
                        break;
                    case 7://millions' range 
                    case 8:
                    case 9:
                        pos = (numDigits % 7) + 1;
                        place = " Million ";
                        break;
                    case 10://Billions's range 
                        pos = (numDigits % 10) + 1;
                        place = " Billion ";
                        break;
                    //add extra case options for anything above Billion... 
                    default:
                        isDone = true;
                        break;
                }
                if (!isDone)
                {//if transalation is not done, continue...(Recursion comes in now!!) 
                    word = translateWholeNumber(number.Substring(0, pos)) + place + translateWholeNumber(number.Substring(pos));
                    //check for trailing zeros 
                    if (beginsZero) word = " and " + word.Trim();
                }
                //ignore digit grouping names 
                if (word.Trim().Equals(place.Trim())) word = "";
            }
        }
        catch { ;}
        return word.Trim();
    }
    private static String tens(String digit)
    {
        int digt = Convert.ToInt32(digit);
        String name = null;
        switch (digt)
        {
            case 10:
                name = "Ten";
                break;
            case 11:
                name = "Eleven";
                break;
            case 12:
                name = "Twelve";
                break;
            case 13:
                name = "Thirteen";
                break;
            case 14:
                name = "Fourteen";
                break;
            case 15:
                name = "Fifteen";
                break;
            case 16:
                name = "Sixteen";
                break;
            case 17:
                name = "Seventeen";
                break;
            case 18:
                name = "Eighteen";
                break;
            case 19:
                name = "Nineteen";
                break;
            case 20:
                name = "Twenty";
                break;
            case 30:
                name = "Thirty";
                break;
            case 40:
                name = "Fourty";
                break;
            case 50:
                name = "Fifty";
                break;
            case 60:
                name = "Sixty";
                break;
            case 70:
                name = "Seventy";
                break;
            case 80:
                name = "Eighty";
                break;
            case 90:
                name = "Ninety";
                break;
            default:
                if (digt > 0)
                {
                    name = tens(digit.Substring(0, 1) + "0") + " " + ones(digit.Substring(1));
                }
                break;
        }
        return name;
    }
    private static String ones(String digit)
    {
        int digt = Convert.ToInt32(digit);
        String name = "";
        switch (digt)
        {
            case 1:
                name = "One";
                break;
            case 2:
                name = "Two";
                break;
            case 3:
                name = "Three";
                break;
            case 4:
                name = "Four";
                break;
            case 5:
                name = "Five";
                break;
            case 6:
                name = "Six";
                break;
            case 7:
                name = "Seven";
                break;
            case 8:
                name = "Eight";
                break;
            case 9:
                name = "Nine";
                break;
        }
        return name;
    }
    private static String translateCents(String cents)
    {
        String cts = "", digit = "", engOne = "";
        for (int i = 0; i < cents.Length; i++)
        {
            digit = cents[i].ToString();
            if (digit.Equals("0"))
            {
                engOne = "Zero";
            }
            else
            {
                engOne = ones(digit);
            }
            cts += " " + engOne;
        }
        return cts;
    }


    internal static int CreateChallan(int ChallanId, int ChallanNo, DateTime ChallanDate, string SuppCompId, string ContractorCompId, string TransportCompId, decimal CFT, string DriverId, string CleanerId, string TruckId, string MaterialId, byte[] ChallanCopy, int UserId,string ticketNo)
    {
        try
        {
            int result = 0;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            SqlParameter param;
            param = new SqlParameter("@ChallanId", Convert.ToInt32(ChallanId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@ChallanNo", ChallanNo);
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@ChallanDate", ChallanDate);
            param.DbType = DbType.DateTime;
            lstparam.Add(param);
            param = new SqlParameter("@SuppCompId", SuppCompId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@ContractorCompId", ContractorCompId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@TransportCompId", TransportCompId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@UserId", Convert.ToInt32(UserId));
            param.DbType = DbType.Int32;
            lstparam.Add(param);
            param = new SqlParameter("@TruckId", TruckId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            if (ChallanCopy.Length > 0)
            {
                param = new SqlParameter("@ChallanCopy", ChallanCopy);
                param.DbType = DbType.Binary;
                lstparam.Add(param);
            }
            param = new SqlParameter("@DriverId", DriverId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CleanerId", CleanerId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@MaterialId", MaterialId);
            param.DbType = DbType.String;
            lstparam.Add(param);
            param = new SqlParameter("@CFT", CFT);
            param.DbType = DbType.Decimal;
            lstparam.Add(param);
            param = new SqlParameter("@TicketNo", ticketNo);
            param.DbType = DbType.String;
            lstparam.Add(param);
            result = ExecuteNonQuery(lstparam, "P_CreateChallan");
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static DataTable GetDashBoardDetails()
    {
        try
        {
            DataTable dt;
            List<SqlParameter> lstparam = new List<SqlParameter>();
            dt = ExecuteDataTable(lstparam, "P_ListDashBoardDetails");
            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }  
    }
}
